package odgs.manager;

import ir.db.DBConnectionManager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import odgs.Contents;
import odgs.ContentsFile;
import odgs.Directory;
import odgs.ExplorerNode;
import odgs.GridNode;
import odgs.Result;

public class MySqlDBManager implements DBManager {

	private String dbDriver = "com.mysql.jdbc.Driver";
	private String dbURL = "jdbc:mysql://222.111.201.49/odgs?user=root&password=1234";
	private String dbUser = "root";
	private String dbPasswd = "1234";

	public MySqlDBManager() {
		try {
			Class.forName(dbDriver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}


	public MySqlDBManager(String dbDriver, String dbURL, String dbUser,	String dbPasswd) {
		this.dbDriver = dbDriver;
		this.dbURL = dbURL;
		this.dbUser = dbUser;
		this.dbPasswd = dbPasswd;
	
	}

	public void freeStorage(String nodeId, long size) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		int tempsize = 0;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("select usedsize from gridnode where nodeid = '" + nodeId + "'");
				tempsize = rs.getInt(1);
				st.executeUpdate("UPDATE gridnode SET usedsize=" + (tempsize - size) 
						+ "where nodeid = '" + nodeId + "'");
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
	}

	public void useStorage(String id, long size) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		int tempsize = 0;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("select usedsize from gridnode where nodeid = '" + id + "'");
				tempsize = rs.getInt(1);
				st.executeUpdate("UPDATE gridnode SET usedsize=" + (tempsize + size) 
						+ "where nodeid like '" + id + "'");
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
	}

	public int addContents(String name, String type, String path, String owner, long size, int permission) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		int pk = -1;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				String qurey = "INSERT INTO Contents VALUES ('" + name + "','"
						+ path + "','" + owner + "','" + type + "'," + size + ","
						+ permission + ")";
				st.executeUpdate(qurey);
				rs = st.executeQuery("select * from contents where name= '" + name + "'and path='"
						+ path + "'and owner='" + owner + "'and type='" + type + "'and size = " + size + "and permission = "
						+ permission);
				pk = rs.getInt("uid");
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return pk;
	}

	public int addContentsFile(String nodeId, int contentsUID, long start, long end) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		int pk = -1;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				String qurey = "INSERT INTO Contentsfile VALUES (" + contentsUID + ",'"
						+ nodeId + "'," + start + "," + end +")";
				st.executeUpdate(qurey);
				rs = st.executeQuery("select * from contentsfile where nodeid= '" + nodeId + "'and contentsuid="
						+ contentsUID + "and start=" + start + "and end=" + end);
				pk = rs.getInt("uid");
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return pk;
	}

	public int addGridNode(String id, long allowedSize, long usedSize) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		int pk = -1;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				String qurey = "INSERT INTO gridnode VALUES ('" + id + "',"
						+ allowedSize + "," + usedSize + "," + 0 + ")";
				st.executeUpdate(qurey);
				rs = st.executeQuery("select * from gridnode where id= '" + id + "'and allowedsize="
						+ allowedSize + "and usedsize=" + usedSize);
				pk = rs.getInt("uid");
	
			} catch (SQLException e) {
				e.printStackTrace();
			}  finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return pk;
	}

	public void freeReservedStorage(String nodeId, long size) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		int tempsize = 0;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("select reservedsize from gridnode where nodeid = '" + nodeId + "'");
				tempsize = rs.getInt(1);
				st.executeUpdate("UPDATE gridnode SET reservedsize=" + (tempsize - size) 
						+ "where nodeid like '" + nodeId + "'");
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
	}

	
	public Connection getConnection()
	{
		Connection con;
		try {
			con = DriverManager.getConnection(dbURL);
			return con;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public Contents getContents(int contentsUID) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		Contents content = null;
		con = getConnection();
		if(con != null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("SELECT * from contents where uid=" + contentsUID);

				if (rs != null) {
					content = new Contents(rs.getString("name"), rs.getString("path"));
					content.setSize(rs.getInt("size"));
					content.setOwner(rs.getString("owner"));
					content.setPermission(rs.getInt("permission"));
					content.setType(rs.getString("type"));
				}
			} catch (SQLException se) {
				System.out.println(se);
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return content;
	}

	private void close(Connection con) {
		try {
			if(con != null)
				con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void close(Statement st) {
		try {
			if(st != null)
				st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void close(ResultSet rs) {
		try {
			if(rs != null)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public int getContentsCount() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		int num = 0;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("select count(*) from contents");
				if (rs != null) {
					num = rs.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return num;
	}

	public List<ContentsFile> getContentsFile(int contentsUID) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		con = getConnection();
		List<ContentsFile> list= new ArrayList<ContentsFile>();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("select * from contentsfile where contentsuid="+ contentsUID);
				if (rs != null) {
					while (rs.next()) {
							ContentsFile file = new ContentsFile(rs.getString("nodeid"));
							file.setContentsUID(rs.getInt("contentsuid"));
							file.setStart(rs.getLong("start"));
							file.setEnd(rs.getLong("end"));
							list.add(file);
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return list;
	}

	public int getContentsFileCount() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		int num = 0;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("select count(*) from contentsfile");
				if (rs != null) {
					num = rs.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return num;
	}

	
	
	public int getDirectoryCount() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		int num = 0;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("select count(*) from directory");
				if (rs != null) {
					num = rs.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return num;
	}


	public GridNode getGridNode(String id) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		GridNode node = null;
		con = getConnection();
		if(con != null)
		{
			try {
				st = con.createStatement();
				rs = st
						.executeQuery("SELECT * from gridnode where id = '"	+ id + "'");
				
				if (rs != null) {
					node = new GridNode(rs.getString("id"), rs
							.getInt("allowedsize"), rs.getInt("usedsize"), rs.getLong("reservedsize"));
				}
			} catch (SQLException se) {
				System.out.println(se);
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}return node;
	}

	public int getGridNodeCount() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		int num = 0;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("select count(*) from gridnode");
				if (rs != null) {
					num = rs.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return num;

	}

	public long getTotalAllowedSize() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		long sum = 0;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("select sum(allowedsize) from gridnode");
				if (rs != null) {
					sum = rs.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return sum;
	}

	public List<Contents> getTotalContents() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		List<Contents> list = new ArrayList<Contents>();
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				rs = st
						.executeQuery("SELECT * from contents");
				if (rs != null) {
					while (rs.next()) {
							Contents conn = new Contents(rs.getString("name"), rs
									.getString("path"));
							conn.setSize(rs.getInt("contentSize"));
							conn.setOwner(rs.getString("owner"));
							conn.setPermission(rs.getInt("permission"));
							conn.setType(rs.getString("type"));
							list.add(conn);
					}
				}
			} catch (SQLException se) {
				System.out.println(se);
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return list;
	}

	public List<Directory> getTotalDirectory() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		List<Directory> list = new ArrayList<Directory>();
		con = getConnection();
		if(con != null)
		{
			try {
				st = con.createStatement();
				rs = st
						.executeQuery("SELECT * from Directory");
				
				if (rs != null) {
					while (rs.next()) {
						Directory dir = new Directory(rs.getString("name"), rs.getString("path"));
						dir.setOwner(rs.getString("owner"));
						dir.setPermission(rs.getInt("permission"));
						list.add(dir);
					}
				}
			} catch (SQLException se) {
				System.out.println(se);
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return list;
	}

	public List<GridNode> getTotalGridNodes() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		List<GridNode> list = new ArrayList<GridNode>();
		con = getConnection();
		if(con != null)
		{
			try {
				st = con.createStatement();
				rs = st
						.executeQuery("SELECT * from gridnode");
				
				if (rs != null) {
					while (rs.next()) {
						GridNode node = new GridNode(rs.getString("id"), rs
								.getInt("allowedsize"), rs.getInt("usedsize"), rs.getLong("reservedsize"));
						list.add(node);
					}
				}
			} catch (SQLException se) {
				System.out.println(se);
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return list;
	}

	public void moveContents(int uid, String destPath) {
		Connection con = null;
		Statement st = null;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				st.executeUpdate("UPDATE contents SET path='" + destPath 
						+ "' where uid like" + uid);
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(st);
				close(con);
			}
		}
	}

	public void moveDirectory(int uid, String destPath) {
		Connection con = null;
		Statement st = null;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				st.executeUpdate("UPDATE directory SET path='" + destPath 
						+ "' where uid like" + uid);
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(st);
				close(con);
			}
		}
	}

	public int removeContents(int uid) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		int result = 0;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				st.executeUpdate("delete from contents where uid=" + uid);
				rs = st.executeQuery("select * from contents where uid=" + uid);
				if(rs == null)
					result = 1;
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		if(result == 1)
			return Result.CONTENTS_REMOVED_OK;
		else
			return Result.CONTENTS_REMOVE_FAIL;
	}

	public int removeContentsFile(int uid) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		int result = 0;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				st.executeUpdate("delete from contentsfile where uid=" + uid);
				rs = st.executeQuery("select * from contentsfile where uid=" + uid);
				if(rs == null)
					result = 1;
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		if(result == 1)
			return Result.CONTENTS_REMOVED_OK;
		else
			return Result.CONTENTS_REMOVE_FAIL;
	}

	public void removeDirectory(int uid) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				st.executeUpdate("delete from directory where uid=" + uid);
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
	}

	public int removeGridNode(String id) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		int result = 0;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				st.executeUpdate("delete from gridnode where id='" + id + "'");
				rs = st.executeQuery("select * from gridnode where id='" + id + "'");
				if(rs == null)
					result = 1;
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		if(result == 1)
			return Result.CONTENTS_REMOVED_OK;
		else
			return Result.CONTENTS_REMOVE_FAIL;
	}

	public void renameContents(int uid, String destName) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				st.executeUpdate("UPDATE contents SET name='" + destName 
						+ "' where uid like" + uid);
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
	}

	public void renameDirectory(int uid, String destName) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				st.executeUpdate("UPDATE directory SET name='" + destName 
						+ "' where uid like" + uid);
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}	
	}

	public void reserveStorage(String nodeId, long size) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		int tempsize = 0;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("select reservedsize from gridnode where nodeid = '" + nodeId + "'");
				tempsize = rs.getInt(1);
				st.executeUpdate("UPDATE gridnode SET reservedsize=" + (tempsize + size) 
						+ "where nodeid like '" + nodeId + "'");
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
	}

	public void updateChildNodePath(String sourcePath, String destPath) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("UPDATE directory SET path = REPLACE(path, '" + 
						sourcePath + "','" + destPath + "') WHERE path LIKE '%" + sourcePath + "%'"); 
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}	
	}

	public int addDirectory(String name, String path, String owner, int permission) {
		Connection con = null;
		Statement st = null;
		ResultSet rs= null;
		int pk = -1;
		con = getConnection();
		if(con != null)
		{
			try {
				st = con.createStatement();
				String qurey = "INSERT INTO directory VALUES ('" + name + "','"
						+ path + "','" + owner + "'," + permission + ")";
				st.executeUpdate(qurey);
				rs = st.executeQuery("select * from gridnode where name= '" + name + "'and path='"
						+ path + "'and owner='" + owner + "'and permission=" + permission);
				pk = rs.getInt("uid");
			} catch (SQLException e) {
				e.printStackTrace();
			}  finally {
				close(rs);
				close(st);
				close(con);
				}
		}
		return pk;
	}

	public long getUsedSize() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		long sum = 0;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("select sum(usedsize) from gridnode");
				if (rs != null) {
					sum = rs.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return sum;
	}

	public List<ExplorerNode> findContentsByName(String keyword) {
		ArrayList<ExplorerNode> list = new ArrayList<ExplorerNode>();
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		con = getConnection();
		if(con!=null)
		{
			try {
				st = con.createStatement();
				rs = st
						.executeQuery("SELECT * from Contents where name ='" + keyword + "'");
				if (rs != null) {
					while (rs.next()) {
							Contents conn = new Contents(rs.getString("name"), rs
									.getString("path"));
							conn.setSize(rs.getInt("size"));
							conn.setOwner(rs.getString("owner"));
							conn.setPermission(rs.getInt("permission"));
							conn.setType(rs.getString("type"));
							list.add(conn);
					}
				}
			} catch (SQLException se) {
				System.out.println(se);
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return list;
	}

	public List<ExplorerNode> findDirectoryByName(String keyword) {
		ArrayList<ExplorerNode> list = new ArrayList<ExplorerNode>();
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		con = getConnection();
		if(con != null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("SELECT * from Directory where name = '" + keyword + "'");
				
				if (rs != null) {
					while (rs.next()) {
						Directory dir = new Directory(rs.getString("name"), rs.getString("path"));
						dir.setOwner(rs.getString("owner"));
						dir.setPermission(rs.getInt("permission"));
						list.add(dir);
					}
				}
			} catch (SQLException se) {
				System.out.println(se);
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return list;
	}


	public Directory getDirectory(int uid){
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		Directory dir = null;
		con = getConnection();
		if(con != null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("SELECT * from directory where uid=" + uid);
				if (rs != null) {
					dir = new Directory(rs.getString("name"), rs.getString("path"));
					dir.setOwner(rs.getString("owner"));
					dir.setPermission(rs.getInt("permission"));
				}
			} catch (SQLException se) {
				System.out.println(se);
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return dir;
	}

	public Contents getContents(String name, String type, String path) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		Contents content = null;
		con = getConnection();
		if(con != null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("SELECT * from contents where name='" + name + "'and type='"+
						type + "'and path='" + path + "'");

				if (rs != null) {
					content = new Contents(rs.getString("name"), rs.getString("path"));
					content.setSize(rs.getInt("size"));
					content.setOwner(rs.getString("Owner"));
					content.setPermission(rs.getInt("Permission"));
					content.setType(rs.getString("Type"));
				}
			} catch (SQLException se) {
				System.out.println(se);
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return content;
	}

	public Directory getDirectory(String name, String path) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		Directory dir = null;
		con = getConnection();
		if(con != null)
		{
			try {
				st = con.createStatement();
				rs = st.executeQuery("SELECT * from directory where name='" + name + "' and path ='" + path + "'");
				if (rs != null) {
					dir = new Directory(rs.getString("name"), rs.getString("path"));
					dir.setOwner(rs.getString("owner"));
					dir.setPermission(rs.getInt("permission"));
				}
			} catch (SQLException se) {
				System.out.println(se);
			} finally {
				close(rs);
				close(st);
				close(con);
			}
		}
		return dir;
	}
}





